﻿Create Procedure [dbo].[accounts_registration_approve]
-- --------------------------------------------------------------------------------- 
-- Procedure Name: accounts_registration_approve
--
-- Description: Approves registration requests
--
--
--
-- Version  Date          Author           Description
-- ---------------------------------------------------------------------------------
--  1       01-Apr-2012   The Collective   Created procedure
-- 
--
--
--
-- ---------------------------------------------------------------------------------
-- ---------------------------------------------------------------------------------
(@UserGUIDListXml as xml, 
 @SecurityCurrentUsername as varchar(255),
 @returncode as int output,
 @returnmessage as varchar(200) output )
AS
BEGIN

    set nocount on

	-- Declarations
    Declare @SecurityCurrentUserGuid Uniqueidentifier;
	Declare @UserGUIDtoApprove Uniqueidentifier;
	Declare @UserGUIDList as Guid_DT;
	Declare @recordcount as int;
	Declare @successcount as int;   

    -- Initialise variables
    Set @returncode = 0;
    Set @returnmessage = '';
	Set @recordcount = 0;
	Set @successcount = 0;
	
    -- Get the guid for the username of the logged in user
    Select @SecurityCurrentUserGuid = User_GUID from Accounts_User where username = @SecurityCurrentUsername;
    
    -- Check that the current user is admin
    If not exists(Select user_GUID from Accounts_User_Roles where User_GUID =  @SecurityCurrentUserGuid and RoleNumber = 101)
       Begin
          -- Exit if user does not have permission
          Set @returncode = 1;
          Set @returnmessage = 'Error. Logged in user is not an administrator';
	      Return 1;
       End;
    
	-- Copy the xml data to table variable
	insert into @UserGUIDList 
    select tab.col.value('@GuidID','uniqueidentifier') from @UserGUIDListXml.nodes('/processlist/regrequest') as tab(col);
	
	-- Declare the processing cursor
	Declare RegCursor cursor for select GuidID FROM @UserGUIDList;

	-- Open the cursor
    open RegCursor;
    fetch next from RegCursor into @UserGUIDtoApprove;
    while @@FETCH_STATUS = 0	
	begin
           begin try
               -- Begin the transaction
               Begin Transaction approve_user_transaction;
		
		       -- Create the user
		       Insert into Accounts_User (User_GUID, username, passwordencrypt)
		       Select User_GUID, email, passwordencrypt from Accounts_Registration_Requests where User_GUID = @UserGUIDtoApprove;
		       
			   -- Count the number of records created
			   set @recordcount = @@rowcount; 

		       -- Insert the user details
		       Insert into Accounts_User_Details (User_GUID, first_name, last_name, organisation, phone_number)
		       Select User_GUID, first_name, last_name, organisation, phone_number from Accounts_Registration_Requests where User_GUID = @UserGUIDtoApprove;
		   
		       -- Remove the registration request
		       Delete from Accounts_Registration_Requests where User_GUID = @UserGUIDtoApprove;
        
               Commit Transaction approve_user_transaction;    
			   
			   -- Count the number of successful transactions, this represents the number of
			   -- accounts successfully created
	           set @successcount = @successcount + @recordcount;      
                 
           end try
           begin catch
               -- If an error occurs then rollback
               Rollback Transaction approve_user_transaction;
        
		       -- Mark the record with a processing error
		       update Accounts_Registration_Requests 
			   set processing_error = 1, 
			       processing_message = 'Could not approve user. Check that user does not already exist.' 
			   where User_GUID = @UserGUIDtoApprove;               
			    
           end catch
    
	      -- Fetch the next record
		  fetch next from RegCursor into @UserGUIDtoApprove;

  	end;  -- while loop

	-- Close the cursor
	close RegCursor;
	deallocate RegCursor;

	Set @returnmessage = CAST(@successcount AS varchar(5)) + ' accounts were created.';

    set nocount off
    
    Return 0;

END;